package com.dao.impl;

import com.bean.Power;
import com.bean.Role;
import com.bean.User;
import com.dao.RoleDao;
import com.util.JDBC_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl implements RoleDao {

    Connection conn = null;
    ResultSet rs= null;
    PreparedStatement ps = null;

    @Override
    public List<Role> getAllRole() {
        conn = JDBC_Util.getConnection();
        List<Role> list = new ArrayList<>();

        String sql = "select t_role.id , role_name from t_role ";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String roleName = rs.getString("role_name");

                Role role = new Role();
                role.setId(id);
                role.setRoleName(roleName);
                list.add(role);
            }
            for (Role role:list) {
                int roleId = role.getId();
                List<Power> list1 = new ArrayList<>();
                String sql1 = "select power_name,tp.id from t_role_power" +
                        " inner join t_power tp on t_role_power.power_id = tp.id" +
                        " where role_id = "+roleId+"";
                ps = conn.prepareStatement(sql1);
                rs = ps.executeQuery();
                while (rs.next()){
                    int powerId = rs.getInt("id");
                    String powerName = rs.getString("power_name");
                    Power power = new Power();
                    power.setId(powerId);
                    power.setPowerName(powerName);
                    list1.add(power);
                }
                role.setPowerList(list1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    @Override
    public List<Role> getRoles(User user) {
        int uId = user.getId();

        conn = JDBC_Util.getConnection();
        List<Role> list = new ArrayList<>();

        String sql = "select t_role.id, role_name from t_role" +
                " inner join t_users_role tur on t_role.id = tur.role_id" +
                " where user_id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uId);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String roleName = rs.getString("role_name");

                Role role = new Role();
                role.setId(id);
                role.setRoleName(roleName);
                list.add(role);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    @Override
    public int addRole(Role role,List<String> powerNames) {
        String roleName = role.getRoleName();
        String sql = "insert into t_role values (null,'"+roleName+"')";
        JDBC_Util.update(sql);
        conn = JDBC_Util.getConnection();

        List<Integer> rIdList = new ArrayList<>();
        String sql1 = "select t_role.id from t_role where role_name = ?";
        int id = 0;
        int rid = 0;
        int m = 0;
        try {
            ps = conn.prepareStatement(sql1);
            ps.setString(1,roleName);
            rs = ps.executeQuery();
            if(rs.next()){
                id = rs.getInt("id");
            }

            for (String powerName:powerNames) {
                String sql2 = "select t_power.id from t_power where power_name = ?";
                ps = conn.prepareStatement(sql2);
                ps.setString(1,powerName);
                rs = ps.executeQuery();
                while (rs.next()) {
                    rid = rs.getInt("id");
                    rIdList.add(rid);
                    System.out.println(rIdList);
                }
            }

            for (int newId:rIdList) {
                String sql3 = "insert into t_role_power values (null,?,?)";
                ps = conn.prepareStatement(sql3);
                ps.setInt(1,id);
                ps.setInt(2,newId);
                m = ps.executeUpdate();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public int deleteRole(int key) {
        String sql = "delete from t_role_power where role_id = "+key+"";
        int m = JDBC_Util.update(sql);
        String sql1 = "delete from t_role where id = "+key+"";
        int n = JDBC_Util.update(sql1);
        return n;
    }

    @Override
    public List<Power> showRolePower(int key) {

        conn = JDBC_Util.getConnection();
        List<Power> list = new ArrayList<>();

        String sql = "select t_power.id, power_name from t_power" +
                " inner join t_role_power trp on t_power.id = trp.power_id" +
                " where role_id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,key);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String power_name = rs.getString("power_name");

                Power power = new Power();
                power.setId(id);
                power.setPowerName(power_name);
                list.add(power);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    @Override
    public int updateRolePower(int key, List<String> powerNames) {
        String str1 = "delete from t_role_power where role_id = "+key+"";
        int x = JDBC_Util.update(str1);

        conn = JDBC_Util.getConnection();
        List<Integer> rIdList = new ArrayList<>();

        int rid = 0;
        int m = 0;
        try {
            for (String powerName:powerNames) {
                String sql2 = "select t_power.id from t_power where power_name = ?";
                ps = conn.prepareStatement(sql2);
                ps.setString(1,powerName);
                rs = ps.executeQuery();
                while (rs.next()) {
                    rid = rs.getInt("id");
                    rIdList.add(rid);
                }
            }

            for (int newId:rIdList) {
                String sql3 = "insert into t_role_power values (null,?,?)";
                ps = conn.prepareStatement(sql3);
                ps.setInt(1,key);
                ps.setInt(2,newId);
                m = ps.executeUpdate();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public String selectRoleName(int key) {
        conn = JDBC_Util.getConnection();

        String sql1 = "select t_role.role_name from t_role where id = ?";

        String name = "";
        try {
            ps = conn.prepareStatement(sql1);
            ps.setInt(1,key);
            rs = ps.executeQuery();
            if(rs.next()){
                name = rs.getString("role_name");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return name;
        }
}
